IF EXISTS (SELECT *
           FROM INFORMATION_SCHEMA.ROUTINES
           WHERE ROUTINE_NAME='pInProdPaper_Update'
           AND ROUTINE_SCHEMA='dbo'
           AND ROUTINE_TYPE='PROCEDURE'
          )
          DROP PROCEDURE dbo.pInProdPaper_Update
GO

CREATE PROC dbo.pInProdPaper_Update
  @vintId        INT,
  @vdatDate      DATETIME
AS
/*@@*_____________________________________________________________________

  

_____________________________________________________________________*@@*/

/*______________________ CONSTANT DECLARATIONS _________________________*/

  DECLARE
    @cstrThisProc              NVARCHAR(128),
    @cintErrGeneral            INT

  SET @cstrThisProc            = N'pInProdPaper_Update'
  SET @cintErrGeneral          = 50000  -- General error. Used when an
                                        -- error has occurred and no other
                                        -- specific error condition is
                                        -- assigned.

/*______________________ VARIABLE DECLARATIONS _________________________*/

  DECLARE
    @intErrNo       INT,
    @strErrMsg      NVARCHAR(400),
    @intReturn      INT

/*_______________________________ INIT _________________________________*/

  -- Setting NOCOUNT ON prevents additional recordsets with the
  -- "number of records affected" from being returned, which is not
  -- handled the same by all providers, such as for OLEDB vs. ODBC.
  SET NOCOUNT ON

/*________________________ VALIDATE PARAMETERS _________________________*/

	DECLARE @oldDate DATETIME
/*_______________________________ MAIN _________________________________*/
	BEGIN TRANSACTION
	BEGIN TRY
		SELECT @oldDate = Date
		FROM InProdPaper
		WHERE Id = @vintId
	
		UPDATE InProdPaper
		SET Date = @vdatDate
		WHERE Id = @vintId
		
		IF DATEDIFF(DAY, @oldDate, @vdatDate) <> 0 BEGIN
			-- Update latest input price of products since @vdatDate was changed
			DECLARE @intProductId INT

			DECLARE CUR_ CURSOR LOCAL FAST_FORWARD FOR 
			SELECT ProductId FROM InProdDetail
			WHERE InProdPaperId = @vintId
			OPEN CUR_
			 
			FETCH NEXT FROM CUR_
			INTO @intProductId 
			  

			WHILE @@FETCH_STATUS = 0
			BEGIN

				EXEC pUpdateLatestInputPriceOfProduct
					@vintProductId = @intProductId
					
				FETCH NEXT FROM CUR_ INTO @intProductId 

			END

			CLOSE CUR_
			DEALLOCATE CUR_
		END
		
		-- Return value 0 indicates success
		SET @intReturn = 0
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		SET @intReturn = -1
		ROLLBACK TRANSACTION
	END CATCH

  RETURN @intReturn

/*___________________________ ERROR HANDLER ____________________________*/
lblError:
  -- An error has occurred.  On entering this section @intErrNo should
  -- already hold the error number

  -- By default, if there are any open transactions then roll them back
  IF (@@TRANCOUNT > 0) BEGIN
    ROLLBACK TRAN

  END

  -- If return value has not been explicitly set, then use the error
  -- number as return value to signify failure
  IF ((@intReturn IS NULL) OR (@intReturn = 0)) BEGIN
    SET @intReturn = @intErrNo

  END


